package com.visionet.waveletData.web.repository.business;

import java.util.Date;
import java.util.List;

import org.springframework.data.domain.Page;
import org.springframework.data.domain.Pageable;
import org.springframework.data.jpa.repository.JpaSpecificationExecutor;
import org.springframework.data.jpa.repository.Modifying;
import org.springframework.data.jpa.repository.Query;
import org.springframework.data.repository.PagingAndSortingRepository;
import org.springframework.data.repository.query.Param;
import org.springframework.transaction.annotation.Transactional;

import com.visionet.waveletData.web.entity.business.UserInfo;

public interface UserInfoDao extends PagingAndSortingRepository<UserInfo, Long>, JpaSpecificationExecutor<UserInfo>{
	
	List<UserInfo> findByIsLock(Integer isLock);
	
	
    @Query("select u from UserInfo u where u.isLock=0 ")
    Page<UserInfo> findAllUserInfoWithAdmin(Pageable page);

	@Query("from UserInfo where isLock=0 and orgId=?1")
	List<UserInfo> findAllUserInfo(Long orgId);
	
	
	@Query("select phoneNumber from UserInfo where isLock=0 and id > 1")
	List<Long> findAllPhoneNumbers();
	
	UserInfo findByEmail(String email);
	
	@Query("from UserInfo where userName like %:userName% and orgId= :orgId and isLock= :isLock ")
	List<UserInfo> findByUserNameFuzzy(@Param("userName") String userName,@Param("orgId") Long orgId,@Param("isLock") Integer isLock);
	
	UserInfo findByUserNameAndOrgIdAndIsLock(String userName,Long orgId,Integer isLock);
	
	@Query("select id,userName from UserInfo where isLock=0 and email in (?1)")
	List<Object[]> findByEmailList(List<String> emails);

    @Query("select id,userName from UserInfo where isLock=0 and (phoneNumber in (?1) or loginName in (?1))")
    List<Object[]> findByPhoneList(List<Long> phoneNumberList);

    @Query("select id,userName,loginName from UserInfo where chexiangAccount in (?1)")
    List<Object[]> findByChexiangList(List<String> chexiangAccountList);

    @Query("select chexiangAccount,chexiangPsw from UserInfo where id = ?1")
    List<Object[]> findChexiangAccountById(Long id);

	@Modifying
	@Query("update UserInfo set isLock=1  where id=?1")
	void deleteByUserId(Long userId);

	@Query("from UserInfo where forWeixin=?1 and orgId =?2")
	UserInfo findByForWeixin(int type, Long orgId);

	@Query("select u from UserInfo u, Relationship r where u.id = r.otherUserId and r.userId=?1 and u.isLock=0 order by u.userName asc")
	Page<UserInfo> findAttentionList(Long userId, Pageable page);
    @Query("select u from UserInfo u, Relationship r where u.id = r.userId and r.otherUserId=?1 and u.isLock=0 order by u.userName asc")
    Page<UserInfo> findFansList(Long userId, Pageable page);

	@Query("select u from UserInfo u, Relationship r where u.id = r.otherUserId and r.userId=:uid and u.isLock=0 and (u.userName like %:userName% or u.firstLetter like %:userName%) order by u.userName asc")
	Page<UserInfo> findAttentionList(@Param("uid") Long currentUserId, @Param("userName") String userName, Pageable page);
    @Query("select u from UserInfo u, Relationship r where u.id = r.userId and r.otherUserId=?1  and u.isLock=0 and (u.userName like %?2% or u.firstLetter like %?2%) order by u.userName asc")
    Page<UserInfo> findFansList(Long currentUserId,String userName, Pageable page);

	@Query("from UserInfo where id= :userId and isLock=0")
	UserInfo findUserById(@Param("userId") Long userId);

	@Query("select u.id from UserInfo u where u.departmentId = ?1")
	List<Long> findUserIdByDepartment(Long departmentId);
	
	@Query("select count(u.id) from UserInfo u where u.id = ?1 and u.trueName !='' and u.departmentId is not null")
	Long findTrueNameDeptExist(Long userId);

    @Query(value="select u.id,u.phone_number,u.user_name,u.true_name,d.department_name,u.create_date,c.last_login,u.integral "+
            "from c_user c,s_user_info u LEFT JOIN c_department d on u.department_id=d.id "+
            "where c.id=u.id and u.org_id = ?1 and u.id > 16  order by u.id ",nativeQuery=true)
	List<Object[]> findExportList(Long orgId);


    @Query(value="SELECT u.id,u.level,u.integral,(SELECT count(r.id) from s_relationship r where r.other_user_id = u.id) as ship "+
            " from s_user_info u where u.is_lock = 0 and u.org_id = ?1 ",nativeQuery=true)
    List<Object[]> findUserLevelList(Long orgId);

    @Modifying
    @Transactional(readOnly = false)
    @Query("update UserInfo set level=?2  where id=?1")
    void updateUserLevel(Long userId,Integer level);

    @Query("select u from UserInfo u where u.isLock=0 and u.orgId=?1 and u.integral is not null order by u.integral desc")
    List<UserInfo> findUsersByIntegral(Long orgId, Pageable page);

    List<UserInfo> findByBadge(Integer badge);

    @Query(value="SELECT u.login_name,u.true_name,u.user_name,u.email,u.integral,u.integral_badge," +
            "(SELECT count(b.id) from s_blog b where b.create_by = u.id and b.del_flag = 0  and b.create_date BETWEEN ?1 and ?2) blogNum," +
            "(SELECT count(c.id) from s_blog_comment c  where c.del_flag = 0 and c.user_id = u.id and c.create_date BETWEEN ?1 and ?2) commentNum ," +
            "(SELECT count(c.id) from s_blog_comment c  where c.del_flag = 0 and c.parent_user_id = u.id and c.create_date BETWEEN ?1 and ?2) beCommentNum ," +
            "(SELECT count(m.id) from s_mention m  where m.ref_type=4 and m.del_flag = 0 and m.ref_own_id = u.id  and m.create_date BETWEEN ?1 and ?2) mentionNum " +
            "from s_user_info u where  u.badge >= 1 and u.org_id = ?3 and u.is_lock = 0 and u.id>9 order by u.true_name ",nativeQuery=true)
    List<Object[]> findModeratorList(Date from,Date to,Long orgId);


    @Query("select u from UserInfo u where u.departmentId = ?1 and u.orgId = ?2  order by u.id")
    List<UserInfo> findUserByDepartment(Long departmentId,Long orgId);


    @Query("select u from UserInfo u where u.departmentId is null and u.orgId = ?1 order by u.id")
    List<UserInfo> findNoDeptUser(Long orgId);

    @Query("select u.id,u.userName,u.integral from UserInfo u where u.isLock=0 and u.integral >= (SELECT u2.integral from UserInfo u2 where u2.id = ?1) order by u.integral asc ")
    List<Object[]> findIntegralRanking1(Long userId,Pageable page);
    @Query("select u.id,u.userName,u.integral from UserInfo u where u.isLock=0 and u.integral < (SELECT u2.integral from UserInfo u2 where u2.id = ?1) order by u.integral desc ")
    List<Object[]> findIntegralRanking2(Long userId,Pageable page);

    @Query(value="select count(u) from UserInfo u where u.isLock=0 and u.integral>=(select i.integral from UserInfo i where i.id=?1)")
    Long findIntegralRankingNum(Long userId);

    @Query(value="SELECT avg(u.integral) from UserInfo u where u.departmentId=?1 and u.isLock=0")
    Float findDeptIntegral(Long deptId);

    @Query(value="SELECT u.integral from UserInfo u where u.id=?1")
    Float findIntegralById(Long userId);

    @Query(value="select u from UserInfo u where u.phoneNumber=?1")
	UserInfo findByPhone(Long phone);

}
